﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace BYManageInfoSystem
{
    class YetAnotherDatagridview2Excel
    {
        public class DataGridViewToExcel
        {
            private Form _OwnerForm;
            private DataGridView _DataGridView;
            private string _FileName = "";

            /// <summary>
            /// 构造函数
            /// </summary>
            /// <param name="ownerForm">拥用者窗体</param>
            /// <param name="dataGridView">DataGridView对象</param>
            public DataGridViewToExcel(Form ownerForm, DataGridView dataGridView)
            {
                _OwnerForm = ownerForm;
                _DataGridView = dataGridView;
            }

            /// <summary>
            /// 导出到Excel
            /// </summary>
            /// <param name="pfileName">文件名,不需要带扩展名</param>
            public void ExportToExcel(string pfileName)
            {
                if (_DataGridView.Rows.Count > 0)
                {
                    _FileName = pfileName == null ? "未命名" : pfileName.Trim();

                    string fileName = ShowSaveFileDialog("Microsoft Excel Document", "Microsoft Excel|*.xls");
                    if (fileName != "")
                    {
                        try
                        {
                            ExportTo(fileName);
                            OpenFile(fileName);
                        }
                        catch (System.Exception err)
                        {
                            MessageBox.Show(err.Message);
                        }
                    }
                }
                else
                {
                    MessageBox.Show("没有可导出的数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
            }

            /// <summary>
            /// 导出到
            /// </summary>
            /// <param name="fileName">文件名</param>
            private void ExportTo(string fileName)
            {
                //
                //获取DataGridView中所有Visible = true的列
                List<DataGridViewColumn> dataGridViewColumns = getDataGridViewColumns();
                //
                //定义一个colCount用于记录当前DataGridView中Visible = true的列数
                int colCount = dataGridViewColumns.Count;
                //
                //定义一个rowCount用于记录当前DataGridView中数据的总行数
                long rowCount = _DataGridView.Rows.Count;
                //
                //声明一Excel Application 对象
                Excel.Application xlApp = new Excel.ApplicationClass();

                xlApp.DisplayAlerts = true;

                xlApp.SheetsInNewWorkbook = 1;

                Excel.Workbook xlBook = xlApp.Workbooks.Add(true);

                Excel.Worksheet ws = (Excel.Worksheet)xlBook.Worksheets[1];

                //
                //定义一个Excel区域对象,用于保存选择的区域
                Excel.Range r = ws.get_Range("A1", NumToExeclRowStr(colCount) + 1);

                //
                //row记录DataGridView中数据的行号,col记录DataGridView中数据的列号,startRow记录在Excel中记录的开始位置
                int row = 0, col = 0, startRow = 1;

                //
                //定义一个object数组dgvHeadText，用于存放DataGridView列头文本
                object[] objHeadText = new object[colCount];
                //
                //循环将DataGridView中所有列的列头文本存入到object一维数组objHeadText
                int i = 0;
                foreach (DataGridViewColumn dgvc in dataGridViewColumns)
                {
                    objHeadText[i++] = dgvc.HeaderText;
                }
                r = ws.get_Range("A" + startRow, NumToExeclRowStr(colCount) + startRow);//设置表头数据区域在Excel中的起始坐标范围
                r = r.get_Resize(1, colCount);//设置表头数据区域在Excel中所占的行数和列数
                r.Value2 = objHeadText;//将数组objHeadText中的所有数据整体写入所设置的表头数据区域

                startRow++;//向Excel中每写一行表头startRow就加一

                //
                //向Excel中写完表头后设置表头背景设和字体
                r.Interior.ColorIndex = 16;
                r.Interior.Pattern = Excel.Constants.xlSolid;
                r.Font.ColorIndex = 2;
                r.HorizontalAlignment = Excel.Constants.xlCenter;
                r.VerticalAlignment = Excel.Constants.xlCenter;

                List<Type> lstType = new List<Type>();

                foreach (DataGridViewColumn dgvc in dataGridViewColumns)
                {
                    lstType.Add(dgvc.ValueType);
                }

                //
                //定义一个和DataGridView中行数和列数同样大小的object二维数组objRecord
                object[,] objRecord = new object[rowCount, colCount];

                //
                //循环将DataGridView中的所有数据存入到object二维数组objRecord
                foreach (DataGridViewRow dgvr in _DataGridView.Rows)
                {
                    col = 0;
                    foreach (DataGridViewColumn dgvc in dataGridViewColumns)
                    {
                        if (lstType[col].Equals(typeof(string)))
                        {
                            objRecord[row, col] = "'" + dgvr.Cells[dgvc.Name].Value.ToString();
                        }
                        else
                        {
                            objRecord[row, col] = dgvr.Cells[dgvc.Name].Value;

                        }
                        col++;
                    }
                    row++;
                }
                r = ws.get_Range("A" + startRow, NumToExeclRowStr(colCount) + (rowCount + 2));//设置数据区域在Excel中的起始坐标范围
                r = r.get_Resize(rowCount, colCount);//设置数据区域在Excel中所占的行数和列数
                r.Value2 = objRecord;//将二维数组objRecord中的所有数据整体写入所设置的数据区域

                r.EntireColumn.AutoFit();//设置Excel内的所有数据列自动调整

                //
                //写入完成后将有数据的范围内设置其边框与内部线条
                ws.UsedRange.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3;
                ws.UsedRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3;
                ws.UsedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = 3;
                ws.UsedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = 3;
                ws.UsedRange.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = 2;
                ws.UsedRange.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = 2;

                xlBook.Saved = true;//设置Wookbook为已保存状态

                try
                {
                    xlBook.SaveCopyAs(fileName);//将当前工作薄保存为指定的文件名
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                xlApp.Quit();//退出Excel程序
            }

            //
            //获取DataGridView中所有Visible = true的列
            private List<DataGridViewColumn> getDataGridViewColumns()
            {
                List<DataGridViewColumn> list = new List<DataGridViewColumn>();
                foreach (DataGridViewColumn dgvc in _DataGridView.Columns)
                {
                    if (dgvc.Visible == true)
                    {
                        list.Add(dgvc);
                    }
                }
                return list;
            }

            private string NumTochr(int num)
            {
                int n = 64 + num;
                return "" + (Char)n;
            }

            //
            //根据DataGridView行数和列数获取Excel的横纵坐标
            private string NumToExeclRowStr(int num)
            {
                int x, y;
                if (num < 27)
                {
                    return NumTochr(num);
                }
                x = num / 26;
                y = num - x * 26;
                return NumTochr(x) + NumTochr(y);
            }

            /// <summary>
            /// 打开文件
            /// </summary>
            /// <param name="fileName">文件名</param>
            private void OpenFile(string fileName)
            {
                if (MessageBox.Show("你想打开这个文件吗?", "导出到...", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    try
                    {
                        System.Diagnostics.Process process = new System.Diagnostics.Process();
                        process.StartInfo.FileName = fileName;
                        process.StartInfo.Verb = "Open";
                        process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;
                        process.Start();
                    }
                    catch
                    {
                        MessageBox.Show(_OwnerForm, "你的计算机中未安装Excel,不能打开该文档!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }

            /// <summary>
            /// 显示保存文件对话框,并返回选择的文件路径
            /// </summary>
            /// <param name="title">对话框的标题</param>
            /// <param name="filter">过滤器</param>
            /// <returns>选择的文件路径</returns>
            private string ShowSaveFileDialog(string title, string filter)
            {
                SaveFileDialog dlg = new SaveFileDialog();
                string name = _FileName;
                int n = name.LastIndexOf(".") + 1;
                if (n > 0) name = name.Substring(n, name.Length - n);
                dlg.Title = "导出到" + title;
                dlg.FileName = name;
                dlg.Filter = filter;
                if (dlg.ShowDialog() == DialogResult.OK) return dlg.FileName;
                return "";
            }
        }
    }
}
